package theDaoImpls;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import DbcpPoolPackage.DbcpPool;
import DbcpPoolPackage.needToClose;
import theBeans.examInfo;
import theBeans.position;
import theDaos.IPositionDao;

public class positionDaoImpl implements IPositionDao{

	@Override
	public List<position> findByIfEmpty() {
		// TODO Auto-generated method stub
		String sql="SELECT * FROM position pid WHERE ifEmpty=1";
		List<position> positionList=new ArrayList<position>();
		Connection conn = null;
		try {
			conn = DbcpPool.getConnection();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		needToClose newNeedtoClose=DbcpPool.executeQuery(conn,sql);
		ResultSet rs=newNeedtoClose.getRs();
		
		try
		{
			while(rs.next())
			{
				position positionOption=new position();
				positionOption.setPid(Integer.parseInt(rs.getString("pid")));
				positionOption.setTime(Timestamp.valueOf(rs.getString("time")));
				positionOption.setIfEmpty(Integer.parseInt(rs.getString("ifEmpty")));
				positionList.add(positionOption);
			}
		}
		catch(SQLException e)
		{
			e.printStackTrace();
		}
		
		DbcpPool.closeAll(newNeedtoClose);
		return positionList;
	}
	
	@Override
	public position findByPid(String pid) {
		// TODO Auto-generated method stub
		String sql="SELECT * FROM position WHERE pid=?";
		position positionOption=new position();
		Connection conn = null;
		try {
			conn = DbcpPool.getConnection();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		needToClose newNeedtoClose=DbcpPool.executePreparedStatement(conn,sql,pid);
		ResultSet rs=newNeedtoClose.getRs();
		
		try
		{
			if(rs.next())
			{
				positionOption.setPid(Integer.parseInt(rs.getString("pid")));
				positionOption.setTime(Timestamp.valueOf(rs.getString("time")));
				positionOption.setIfEmpty(Integer.parseInt(rs.getString("ifEmpty")));
			}
		}
		catch(SQLException e)
		{
			e.printStackTrace();
		}
		
		DbcpPool.closeAll(newNeedtoClose);
		return positionOption;
	}
	
	@Override
	public int positionLeft() {
		// TODO Auto-generated method stub
		String sql="SELECT count(*) FROM position where ifEmpty=1";
		int positionLeftNum=0;
		Connection conn = null;
		try {
			conn = DbcpPool.getConnection();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		needToClose newNeedtoClose=DbcpPool.executeQuery(conn,sql);
		ResultSet rs=newNeedtoClose.getRs();
		
		try
		{
			while(rs.next())
			{
				positionLeftNum=Integer.parseInt(rs.getString("count"));
			}
		}
		catch(SQLException e)
		{
			e.printStackTrace();
		}
		
		DbcpPool.closeAll(newNeedtoClose);
		return positionLeftNum;
	}

	@Override
	public int update(String pid,String ifEmpty) {
		// TODO Auto-generated method stub
		String sql="UPDATE position SET ifEmpty=? WHERE pid=?";
		Connection conn=null;
		try {
			conn=DbcpPool.getConnection();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		int result=0;
		PreparedStatement ps=DbcpPool.executePreparedStatementReturnPS(conn, sql);
		try
		{
			ps.setString(1, ifEmpty);
			ps.setString(2, pid);
			result=ps.executeUpdate();
		}
		catch(SQLException e)
		{
			e.printStackTrace();
		}
		
		needToClose newNeedtoClose=new needToClose();
		newNeedtoClose.setAll(conn, ps, null);
		
		DbcpPool.closeAll(newNeedtoClose);
		return result;
	}


}
